/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package accesodatos;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;

/**
 *
 * @author User
 */
//En esta clase pretendo hacer todas las consultas necesarias para traer los datos de la BD transaccional
//y procesarlos para que queden listos para que queden actos para ingresarlo a la DW
public class DaoDimensiones {

    private FachadaBD fachadabd; //Conectarse a BD trans.
    private FachadaDW fachadadw; // Conectarse a DW.

    public DaoDimensiones() {
        fachadadw = new FachadaDW();
        fachadabd = new FachadaBD();
    }

    public ArrayList<String> getMedicos() {

        //Extrae a todos los médicos de la BD transaccional y dado que en la DW no se pueden tener valores nulos
        //se verifica si el médico tiene especialidad, subespecialidad o una dirección de consultorio,
        // en caso de no tenerlas se pone el valor "Ninguna"
        
        ArrayList<String> resultadosmedicos = new ArrayList<>();

        String sql_select;
        sql_select = "SELECT * FROM medico";
        try {
            Connection conn = fachadabd.conectar();
            Statement sentencia = conn.createStatement();
            ResultSet tabla = sentencia.executeQuery(sql_select);
            //System.out.println("Codigo\tNombre\tPrograma");
            //
            String resultado = "";
            while (tabla.next()) {
                resultado = "";
                resultado += "'" + tabla.getString("Cedula") + "'" + "," + "'" + tabla.getString("Nombre") + "'" + ",";
                if (tabla.getString("Especialidad").equals("") || tabla.getString("Especialidad").equals(" ")) {
                    resultado += "'Ninguna',";
                } else {
                    resultado += "'" + tabla.getString("Especialidad") + "'" + ",";
                }

                if (tabla.getString("Subespecialidad").equals("") || tabla.getString("Subespecialidad").equals(" ")) {
                    resultado += "'Ninguna',";
                } else {
                    resultado += "'" + tabla.getString("Subespecialidad") + "'" + ",";
                }


                resultado += "'" + tabla.getString("Licencia") + "'" + ",";
                if (tabla.getString("Direccion_Consultorio").equals("") || tabla.getString("Direccion_Consultorio").equals(" ")) {
                    resultado += "'Ninguna'";
                } else {
                    resultado += "'" + tabla.getString("Direccion_Consultorio") + "'";
                }
                // "'"+tabla.getString("Direccion_Consultorio")+"'";         
                //estudiante.setPrograma(tabla.getString("programa"));
                resultadosmedicos.add(resultado);
            }
            conn.close();
            return resultadosmedicos;


        } catch (SQLException e) {
            System.out.println(e);
        } catch (Exception e) {
            System.out.println(e);
        }
        
        return resultadosmedicos;
    }

    public ArrayList<String> getIps() {
        ArrayList<String> ipss = new ArrayList<>();

        String sql_select;
        sql_select = "SELECT * FROM ips";
        try {
            Connection conn = fachadabd.conectar();
            Statement sentencia = conn.createStatement();
            ResultSet tabla = sentencia.executeQuery(sql_select);

            String resultado = "";
            while (tabla.next()) {
                resultado = "";
                resultado += "'" + tabla.getString("ID_IPS") + "',"
                        + "'" + tabla.getString("Tipo_IPS") + "',"
                        + "'" + tabla.getString("Nombre") + "',"
                        + "'" + tabla.getString("Direccion") + "',";
                if (tabla.getString("Nivel").equals("") || tabla.getString("Nivel").equals(" ")) {
                    resultado += "'N/A',";
                } else {
                    resultado += "'" + tabla.getString("Nivel") + "',";
                }

                resultado += "'" + getIdCiudadInDimension(tabla.getString("Municipio")) + "'";


                ipss.add(resultado);
            }
            conn.close();
            return ipss;


        } catch (SQLException e) {
            System.out.println(e);
        } catch (Exception e) {
            System.out.println(e);
        }
        return null;
    }

    public ArrayList<String> getEmpleadores() {

        ArrayList<String> empleadores = new ArrayList<>();
        String sql_select;
        sql_select = "SELECT * FROM empresa";
        try {
            Connection conn = fachadabd.conectar();
            Statement sentencia = conn.createStatement();
            ResultSet tabla = sentencia.executeQuery(sql_select);

            String resultado = "";
            while (tabla.next()) {
                resultado = "";
                resultado += "'" + tabla.getString("NIT") + "',"
                        + "'" + tabla.getString("Nombre") + "',"
                        + "'" + tabla.getInt("Total_Empleados") + "',"
                        + "'" + tabla.getString("Direccion") + "',"
                        + "'" + tabla.getString("Actividad_Economica") + "',"
                        + "'" + tabla.getString("Ciudad") + "'";
                empleadores.add(resultado);
            }
            conn.close();
            return empleadores;


        } catch (SQLException e) {
            System.out.println(e);
        } catch (Exception e) {
            System.out.println(e);
        }
        return null;


    }

    public ArrayList<String> getMedicamentos() {
        //La Dimensión Medicamento debería tener el precio no? (en el modelo no está) 
        ArrayList<String> medicamentos = new ArrayList<>();
        String sql_select;
        sql_select = "SELECT * FROM medicamento";
        try {
            Connection conn = fachadabd.conectar();
            Statement sentencia = conn.createStatement();
            ResultSet tabla = sentencia.executeQuery(sql_select);

            String resultado = "";
            while (tabla.next()) {
                resultado = "";
                resultado += "'" + tabla.getString("codigo") + "',"
                        + "'" + tabla.getString("nombre_generico") + "',"
                        + "'" + tabla.getString("forma_farmaceutica") + "',"
                        + "'" + tabla.getString("presentacion") + "',"
                        + "'" + tabla.getString("laboratorio_registro") + "',"
                        + "'" + tabla.getString("tipo_medicamento") + "'";
                medicamentos.add(resultado);
            }
            conn.close();
            return medicamentos;


        } catch (SQLException e) {
            System.out.println(e);
        } catch (Exception e) {
            System.out.println(e);
        }
        return null;
    }

    public ArrayList<String> getDemografiaCotizante() {
        //Sexo, estado_civil,nivel_escolaridad, direccion, estrato, salario_base

        ArrayList<String> demoCotizante = new ArrayList<>();
        String sql_select;
        sql_select = "SELECT sexo, estado_civil, nivel_escolaridad,direccion,"
                + " estracto, salario_base FROM cotizante;";
        try {
            Connection conn = fachadabd.conectar();
            Statement sentencia = conn.createStatement();
            ResultSet tabla = sentencia.executeQuery(sql_select);

            String resultado = "";
            while (tabla.next()) {
                resultado = "";
                resultado += "'" + tabla.getString("sexo") + "',"
                        + "'" + tabla.getString("estado_civil") + "',"
                        + "'" + tabla.getString("nivel_escolaridad") + "',"
                        + "'" + tabla.getString("direccion") + "',"
                        + "'" + tabla.getString("estracto") + "',"
                        + "'" + tabla.getInt("salario_base") + "'";
                demoCotizante.add(resultado);
            }
            conn.close();
            return demoCotizante;
        } catch (SQLException e) {
            System.out.println(e);
        } catch (Exception e) {
            System.out.println(e);
        }
        return null;
    }

    public ArrayList<String> getDemografiaBeneficiario() {
        //id_a, estado_civil,nivel_escolaridad, direccion, estrato, salario_base

        ArrayList<String> demoBeneficiario = new ArrayList<>();
        String sql_select;
        sql_select = "SELECT a1.Sexo, a1.Estado_Civil, c.Estracto, c.Direccion,c.Salario_Base FROM\n"
                + "	(SELECT cb.Cotizante, b.Sexo, b.Estado_Civil\n"
                + " FROM (cotizante_beneficiario cb JOIN beneficiario b  on cb.beneficiario= b.ID_Beneficiario)) a1 JOIN cotizante c On a1.Cotizante=c.Cedula;";
        try {
            Connection conn = fachadabd.conectar();
            Statement sentencia = conn.createStatement();
            ResultSet tabla = sentencia.executeQuery(sql_select);

            String resultado = "";
            while (tabla.next()) {
                resultado = "";
                resultado += "'" + tabla.getString("Sexo") + "',"
                        + "'" + tabla.getString("Estado_Civil") + "',"
                        + "'NO APLICA',"
                        + "'" + tabla.getString("Direccion") + "',"
                        + "'" + tabla.getString("Estracto") + "',"
                        + "'" + tabla.getInt("Salario_Base") + "'";
                demoBeneficiario.add(resultado);
            }
            conn.close();
            return demoBeneficiario;
        } catch (SQLException e) {
            System.out.println(e);
        } catch (Exception e) {
            System.out.println(e);
        }
        return null;
    }

    public ArrayList<String> getDepartamentos() {

        ArrayList<String> departamento = new ArrayList<>();
        String sql_select;
        sql_select = "SELECT DISTINCT departamento FROM ips;";
        try {
            Connection conn = fachadabd.conectar();
            Statement sentencia = conn.createStatement();
            ResultSet tabla = sentencia.executeQuery(sql_select);

            String resultado = "";
            while (tabla.next()) {
                resultado = "";
                resultado += "'" + tabla.getString("departamento") + "'";

                departamento.add(resultado);
            }
            conn.close();
            return departamento;
        } catch (SQLException e) {
            System.out.println(e);
        } catch (Exception e) {
            System.out.println(e);
        }
        return null;
    }

    public ArrayList<String> getDemografia() {
        ArrayList<String> demografia = new ArrayList<>();
        ArrayList<String> demoCotizante = getDemografiaCotizante();
        ArrayList<String> demoBeneficiario = getDemografiaBeneficiario();

        for (int i = 0; i < demoCotizante.size(); i++) {
            demografia.add(demoCotizante.get(i));
        }
        for (int i = 0; i < demoBeneficiario.size(); i++) {
            demografia.add(demoBeneficiario.get(i));
        }
        HashSet<String> hash = new HashSet();
        hash.addAll(demografia);
        demografia.clear();
        //agregar elementos a diagnosticos sin repetidos
        demografia.addAll(hash);

        return demografia;

    }

    public ArrayList<String> getCiudades() {
        // LOS DEPARTAMENTOS DEBEN ESTAR PREVIAMENTE CARGADOS EN LA DW
        ArrayList<String> ciudaddes = new ArrayList<>();
        String sql_select;
        sql_select = "SELECT DISTINCT municipio,departamento FROM ips;";
        try {
            Connection conn = fachadabd.conectar();
            Statement sentencia = conn.createStatement();
            ResultSet tabla = sentencia.executeQuery(sql_select);

            String resultado = "";
            while (tabla.next()) {
                resultado = "";
                resultado += "'" + tabla.getString("municipio") + "',";
                resultado += "'" + getIdDepartamentoInDimension(tabla.getString("departamento")) + "'";

                ciudaddes.add(resultado);
            }
            conn.close();
            return ciudaddes;
        } catch (SQLException e) {
            System.out.println(e);
        } catch (Exception e) {
            System.out.println(e);
        }
        return null;
    }

    public ArrayList<String> getServiciosPOS() {
        ArrayList<String> servicios = new ArrayList<>();
        String sql_select;
        sql_select = "SELECT DISTINCT ID_Servicio_POS, Descripcion FROM servicios_pos;";
        try {
            Connection conn = fachadabd.conectar();
            Statement sentencia = conn.createStatement();
            ResultSet tabla = sentencia.executeQuery(sql_select);

            String resultado = "";
            while (tabla.next()) {
                resultado = "";
                resultado += "'" + tabla.getString("ID_Servicio_POS") + "',";
                resultado += "'" + tabla.getString("Descripcion") + "'";

                servicios.add(resultado);
            }
            conn.close();
            return servicios;
        } catch (SQLException e) {
            System.out.println(e);
        } catch (Exception e) {
            System.out.println(e);
        }
        return null;
    }

    public ArrayList<String> getAfiliadosBeneficiarios() {

        //tipo_afiliacion, tipo_identificacion, identificacion, nombre, fecha_nacimiento, tipo_discapacidad, tipo_cotizante, eps_previa
        ArrayList<String> afiliadosBen = new ArrayList<>();
        String sql_select;
        sql_select = "SELECT  ID_Beneficiario,Tipo_Identificacion, nombre, Fecha_Nacimiento, Tipo_Discapacidad FROM Beneficiario;";
        try {
            Connection conn = fachadabd.conectar();
            Statement sentencia = conn.createStatement();
            ResultSet tabla = sentencia.executeQuery(sql_select);

            String resultado = "";
            while (tabla.next()) {
                resultado = "";
                resultado += "'BENEFICIARIO',";
                resultado += "'" + tabla.getString("Tipo_Identificacion") + "',";
                resultado += "'" + tabla.getString("ID_Beneficiario") + "',";
                resultado += "'" + tabla.getString("nombre") + "',";
                resultado += "'" + tabla.getDate("Fecha_Nacimiento") + "',";
                resultado += "'" + tabla.getString("Tipo_Discapacidad") + "',";
                resultado += "'NO APLICA',";
                resultado += "'NO APLICA'";

                afiliadosBen.add(resultado);
            }
            conn.close();
            return afiliadosBen;
        } catch (SQLException e) {
            System.out.println(e);
        } catch (Exception e) {
            System.out.println(e);
        }
        return null;
    }

    public ArrayList<String> getAfiliadosCotizante() {

        //tipo_afiliacion, tipo_identificacion, identificacion, nombre, fecha_nacimiento, tipo_discapacidad, tipo_cotizante, eps_previa
        ArrayList<String> afiliadosCotizante = new ArrayList<>();
        String sql_select;
        sql_select = "SELECT Cedula, Nombre, Tipo_Cotizante,Fecha_Nacimiento,Proviene_Otra_EPS, Tipo_Discapacidad  FROM Cotizante;";
        try {
            Connection conn = fachadabd.conectar();
            Statement sentencia = conn.createStatement();
            ResultSet tabla = sentencia.executeQuery(sql_select);

            String resultado = "";
            while (tabla.next()) {
                resultado = "";
                resultado += "'COTIZANTE',";
                resultado += "'Cedula',";
                resultado += "'" + tabla.getString("Cedula") + "',";
                resultado += "'" + tabla.getString("Nombre") + "',";
                resultado += "'" + tabla.getDate("Fecha_Nacimiento") + "',";
                resultado += "'" + tabla.getString("Tipo_Discapacidad") + "',";
                resultado += "'" + tabla.getString("Tipo_Cotizante") + "',";
                if (tabla.getString("Proviene_Otra_EPS").equals("1")) {
                    resultado += "'SI'";
                } else if (tabla.getString("Proviene_Otra_EPS").equals("0")) {
                    resultado += "'NO'";
                }
                //resultado+= "'"+ tabla.getString("Proviene_Otra_EPS")+"'";
               // System.out.println(resultado);
                afiliadosCotizante.add(resultado);
            }
            conn.close();
            return afiliadosCotizante;
        } catch (SQLException e) {
            System.out.println(e);
        } catch (Exception e) {
            System.out.println(e);
        }
        return null;
    }

    public ArrayList<String> getAfiliados() {
        ArrayList<String> afiBen = getAfiliadosBeneficiarios();
        ArrayList<String> afiCot = getAfiliadosCotizante();
        ArrayList<String> afiliados = new ArrayList<>();
        for (int i = 0; i < afiBen.size(); i++) {
            afiliados.add(afiBen.get(i));
        }
        for (int i = 0; i < afiCot.size(); i++) {
            afiliados.add(afiCot.get(i));
        }
        return afiliados;
    }

    public String getIdCiudadInDimension(String nombreCiudad) {
        String ciudadResult = "";
        String sql_select;
        sql_select = "SELECT id_ciudad FROM ciudad WHERE ciudad= '" + nombreCiudad + "'";
        try {
            Connection conn = fachadadw.conectar();
            Statement sentencia = conn.createStatement();
            ResultSet tabla = sentencia.executeQuery(sql_select);

            while (tabla.next()) {
                ciudadResult = tabla.getString("id_ciudad");

                //estudiante.setPrograma(tabla.getString("programa"));
            }
            conn.close();
            return ciudadResult;


        } catch (SQLException e) {
            System.out.println(e);
        } catch (Exception e) {
            System.out.println(e);
        }
        return null;

    }

    public String getIdDepartamentoInDimension(String nombreDep) {
        //Este método se conecta con la Bodega de Datos, no con la BD transaccional 
        String departResult = "";
        String sql_select;
        sql_select = "SELECT id_departamento FROM departamento WHERE departamento= '" + nombreDep + "'";
        try {
            Connection conn = fachadadw.conectar();
            Statement sentencia = conn.createStatement();
            ResultSet tabla = sentencia.executeQuery(sql_select);

            while (tabla.next()) {
                departResult = tabla.getString("id_departamento");

                //estudiante.setPrograma(tabla.getString("programa"));
            }
            conn.close();
            return departResult;


        } catch (SQLException e) {
            System.out.println(e);
        } catch (Exception e) {
            System.out.println(e);
        }
        return null;
    }

    public ArrayList<String> getDiagnosticos() {

        ArrayList<String> diagnosticos = new ArrayList<>();
        String sql_select_diag_urgencias = "SELECT DISTINCT Diagnostico from urgencias ";
        String sql_select_diag_hosp = "SELECT Distinct Diagnostico from hospitalizaciones ";
        String sql_select_diag_citas = "SELECT Distinct Diagnostico from citas_generales ";
        String sql_select_diag_remisiones = "SELECT Distinct Diagnostico from remisiones ";

        try {

            Connection conn = fachadabd.conectar();
            Statement sentencia = conn.createStatement();

            ResultSet tabla1 = sentencia.executeQuery(sql_select_diag_urgencias);
            while (tabla1.next()) {
                String resultado = "";
                resultado = "'" + tabla1.getString("Diagnostico") + "'";
                diagnosticos.add(resultado);
                // System.out.println("first");
            }

            ResultSet tabla2 = sentencia.executeQuery(sql_select_diag_hosp);
            while (tabla2.next()) {

                String resultado = "";
                resultado = "'" + tabla2.getString("Diagnostico") + "'";
                diagnosticos.add(resultado);
                // System.out.println("Second");
            }

            ResultSet tabla3 = sentencia.executeQuery(sql_select_diag_citas);
            while (tabla3.next()) {

                String resultado = "";
                resultado = "'" + tabla3.getString("Diagnostico") + "'";
                diagnosticos.add(resultado);
                //System.out.println("Third");
            }

            ResultSet tabla4 = sentencia.executeQuery(sql_select_diag_remisiones);
            while (tabla4.next()) {

                String resultado = "";
                resultado = "'" + tabla4.getString("Diagnostico") + "'";
                diagnosticos.add(resultado);
                // System.out.println("Last");
            }


            //Creacion objeto HashSet para quitar Strings repetidos de un arrayList
            HashSet<String> hash = new HashSet();
            hash.addAll(diagnosticos);

            //limpiar arrayList diagnosticos para insertar de nuevo elementos
            diagnosticos.clear();
            //agregar elementos a diagnosticos sin repetidos
            diagnosticos.addAll(hash);

            conn.close();

        } catch (SQLException e) {
            System.out.println(e);

        } catch (Exception e) {

            System.out.println(e.getMessage());
        }

        return diagnosticos;

    }

    public ArrayList<String> getFechas() {
        ArrayList<String> fechas = new ArrayList<>();
        String sql_select;
        sql_select = "SELECT * FROM fecha ORDER BY anio;";
        
        try {
            Connection conn = fachadabd.conectar();
            Statement sentencia = conn.createStatement();
            ResultSet tabla = sentencia.executeQuery(sql_select);

            String resultado = "";
            while (tabla.next()) {
                resultado = "";
                resultado += "'" + tabla.getString("anio") + "-" + tabla.getString("mes") + "-" + tabla.getString("dia") + "',";
                resultado += "'" + tabla.getString("dia") + "',";
                resultado += "'" + tabla.getString("mes") + "',";
                resultado += "'" + tabla.getString("anio") + "',";
                resultado += "'" + tabla.getString("nombre_dia") + "',";
                resultado += "'" + tabla.getString("nombre_mes") + "',";
                resultado += "'" + tabla.getString("festivo") + "',";
                resultado += "'" + tabla.getString("nombre_festivo") + "'";
                
                //System.out.println(resultado);

                fechas.add(resultado);
            }
            conn.close();
            return fechas;
        } catch (SQLException e) {
            System.out.println(e);
        } catch (Exception e) {
            System.out.println(e);
        }
        return null;
    }
    
    public ArrayList<ArrayList <String>> getGrupoFamiliar(){
    
        
        ArrayList<ArrayList <String>> grupos = new ArrayList();
        
        String sql_selectDB = "SELECT Cedula FROM cotizante;";
        String sql_select2DB ;
        String sql_selectDW;
        int idGrupo = 1;
        
        try {
            Connection connDB = fachadabd.conectar();
            Connection connDW = fachadadw.conectar();
            
            Statement sentencia1DB = connDB.createStatement();
            Statement sentencia2DB = connDB.createStatement();
            Statement sentencia3DB = connDB.createStatement();
            Statement sentencia1DW = connDW.createStatement();
            Statement sentencia2DW = connDW.createStatement();
            
            ResultSet tablaCotizantes = sentencia1DB.executeQuery(sql_selectDB);
            
            while(tablaCotizantes.next()){
                
                ArrayList<String> grupoFamiliar = new ArrayList();
                
                sql_select2DB = "SELECT * FROM cotizante_beneficiario WHERE cotizante = '"
                        +tablaCotizantes.getString("Cedula") +"';";
                ResultSet tablaRelacion = sentencia2DB.executeQuery(sql_select2DB);
                
                
                sql_selectDW = "SELECT id_afiliado FROM dimension_afiliado WHERE identificacion = '"+ tablaCotizantes.getString("Cedula") +"';";
                ResultSet idCotizante = sentencia1DW.executeQuery(sql_selectDW);
                idCotizante.next();
                
                String cotizante = "'"+idGrupo+"','"+idCotizante.getString("id_afiliado") +"','NO APLICA','"+idCotizante.getString("id_afiliado")+"'"; 
                grupoFamiliar.add(cotizante);
                
                
                String resultado = "";
                
                while(tablaRelacion.next()){
                    
                    ResultSet idBeneficiario = sentencia2DW.executeQuery
                            ("SELECT id_afiliado FROM dimension_afiliado WHERE identificacion = '"+tablaRelacion.getString("Beneficiario") +"';");
                    ResultSet datosBeneficiario = sentencia3DB.executeQuery
                            ("SELECT parentesco FROM beneficiario WHERE ID_Beneficiario = '"+tablaRelacion.getString("Beneficiario")+"';");
                    datosBeneficiario.next();
                    idBeneficiario.next();
                    
                    resultado = "";
                    resultado += "'"+ idGrupo +"','"+idBeneficiario.getString("id_afiliado");
                    resultado += "','"+datosBeneficiario.getString("parentesco");
                    resultado+="','"+idCotizante.getString("id_afiliado")+"'";
                    System.out.println(resultado);
                    grupoFamiliar.add(resultado);
                }
                
                grupos.add(grupoFamiliar);
                idGrupo++;
            }
            connDB.close();
            return grupos;
        
        }catch(SQLException sql){
        
            System.err.println(sql.getMessage());
        
        }catch(Exception e){
        
            System.err.println(e);
        
        }
        
        return grupos;
        
    
    }

//    public static void main(String Args[]) {
//
//        DaoDimensiones d = new DaoDimensiones();
//       // d.getGrupoFamiliar();
//        /*ArrayList resultados = d.getGrupoFamiliar();
//        
//        for (int i = 0 ; i < resultados.size(); i++){
//        
//            ArrayList grupo = (ArrayList)resultados.get(i);
//            for (int j = 0 ; j< grupo.size() ; j++){
//            
//                System.out.println(grupo.get(j));
//            }
//            
//        }
//  */
//
//
//
//    }
}